import openpyxl as op

from .excelconstants import excel_sheet_names, cure_table_of_qita
from .excelconstants import cc_table_of_qita, table_contain_qita
from .excelconstants import relevant_table_of_qita, history_table_of_info
from .excelconstants import info_table_of_info

from .mobile import mobile_save_instance

from prj001.models.geninfo import GeneralInfo

from .updatefile import update_model

from .excelconstants import true_change_bool


def read_info_key(info_sheet, data, i, col=None):
    key = info_sheet['%s%d' % (col, i)].value
    if key:
        key = str(key).strip()
    return key


def read_info_value(info_sheet, data, i, col=None):
    value = info_sheet['%s%d' % (col, i)].value
    if value:
        value = str(value).strip()
    return value


def read_info(info_sheet, data, i, key_col=None, value_col=None, table_name=None):
    key = read_info_key(info_sheet, data, i, col=key_col)
    value = read_info_value(info_sheet, data, i, col=value_col)
    if table_name in table_contain_qita:
        if key not in data.get(table_name).keys() and key is not None:
            if key == 'reducefat_ever' or key == 'menstruation_is_accompany':
                if value == '有':
                    data.get(table_name)[key] = True
                else:
                    data.get(table_name)[key] = False
            else:
                data.get(table_name)[key] = value
        else:
            pass
    else:
        data.get(table_name)[key] = value


def read_row_into_const(sheetname, endrow, col='E', into_list=None):
    for i in range(2, endrow + 1):
        if sheetname['%s%d' % (col, i)].value:
            into_list.append(i)


def checked_id(data, request_param=None):
    '''if info_exist: True'''
    info_serial = data['info'].get('serial')
    info_obj = None
    if info_serial:
        try:
            info_obj = GeneralInfo.objects.get(serial=info_serial)
        except GeneralInfo.DoesNotExist:
            raise ValueError('创建数据时,不需要填写问卷编码')
    if info_obj:
        return info_obj
    return False


def bool_to_str(data):
    history = data.get('history')
    for key, value in true_change_bool.items():
        if isinstance(history.get(key), bool) and not history.get(key):
            history[key] = None
        else:
            history[key] = 'true'


# info 41
def gen_info_table(wb, data, endrow=None, endrow_of_other=None):
    info_sheet = wb['一般情况']

    read_row_into_const(info_sheet, endrow, into_list=info_table_of_info)

    for i in info_table_of_info:
        read_info(info_sheet, data, i, key_col='E',
                  value_col='B', table_name='info')

    info_sheet_checkbox = wb['一般情况-']

    for i in range(2, endrow_of_other + 1):
        read_info(info_sheet_checkbox, data, i, key_col='A',
                  value_col='C', table_name='info')
        read_info(info_sheet_checkbox, data, i, key_col='E',
                  value_col='F', table_name='info')


# summary
def summary_table(wb, data, endrow=None, endrow_of_other=None):
    summary_sheet = wb['就诊时病情概要']

    for i in range(2, endrow + 1):
        read_info(summary_sheet, data, i, key_col='E',
                  value_col='B', table_name='summary')

    summary_sheet_checkbox = wb['病情概要']

    for i in range(1, endrow_of_other + 1):
        read_info(summary_sheet_checkbox, data, i, key_col='B',
                  value_col='D', table_name='summary')


# cc 13 48
def cc_table(wb, data, endrow=None, endrow_of_other=None):
    cc_sheet = wb['临床诊断']

    read_row_into_const(cc_sheet, endrow, into_list=cc_table_of_qita)

    for i in cc_table_of_qita:
        read_info(cc_sheet, data, i, key_col='E',
                  value_col='B', table_name='cc')

    cc_sheet_checkbox = wb['诊断']

    # 诊断的表中: 前46行都不需要
    for i in range(46, endrow_of_other + 1):
        read_info(cc_sheet_checkbox, data, i, key_col='B',
                  value_col='D', table_name='cc')


# cure 130 142
def cure_table(wb, data, endrow=None, endrow_of_other=None):
    cure_sheet = wb['治疗']

    read_row_into_const(cure_sheet, endrow, into_list=cure_table_of_qita)

    for i in cure_table_of_qita:
        read_info(cure_sheet, data, i, key_col='E',
                  value_col='B', table_name='cure')

    cure_sheet_checkbox = wb['治疗及检查']

    for i in range(1, endrow_of_other + 1):
        read_info(cure_sheet_checkbox, data, i, key_col='A',
                  value_col='C', table_name='cure')


# results
def results_table(wb, data, endrow=None, endrow_of_other=None):
    results_sheet = wb['疗效']

    for i in range(2, 4):
        read_info(results_sheet, data, i, key_col='E',
                  value_col='B', table_name='results')


# relevant 17 16
def relevant_table(wb, data, endrow=None, endrow_of_other=None):
    relevant_sheet = wb['相关检查']

    read_row_into_const(relevant_sheet, endrow,
                        into_list=relevant_table_of_qita)

    for i in relevant_table_of_qita:
        read_info(relevant_sheet, data, i, key_col='E',
                  value_col='B', table_name='relevant')

    relevant_sheet_checkbox = wb['治疗及检查']

    for i in range(1, endrow_of_other + 1):
        read_info(relevant_sheet_checkbox, data, i, key_col='E',
                  value_col='G', table_name='relevant')


# history 154+5 140+4
def history_table(wb, data, endrow=None, endrow_of_other=None):
    history_sheet = wb['病史']

    read_row_into_const(history_sheet, endrow, into_list=history_table_of_info)

    for i in history_table_of_info:
        read_info(history_sheet, data, i, key_col='E',
                  value_col='B', table_name='history')

    history_sheet_checkbox = wb['病史1']

    for i in range(1, endrow_of_other + 1):
        read_info(history_sheet_checkbox, data, i, key_col='B',
                  value_col='D', table_name='history')


def analyse_excel(file_stream, sf=None, request_param=None):
    # step 1: analyze
    wb = op.load_workbook(file_stream)

    ws_list = wb.sheetnames

    if not (ws_list == excel_sheet_names):
        raise ValueError('上传的文件非模板文件')

    data = {
        'info': dict(),
        'cure': dict(),
        'cc': dict(),
        'results': dict(),
        'relevant': dict(),
        'summary': dict(),
        'history': dict()
    }

    try:
        gen_info_table(wb, data, endrow=41, endrow_of_other=11)
        summary_table(wb, data, endrow=13, endrow_of_other=190)
        cure_table(wb, data, endrow=136, endrow_of_other=150)
        cc_table(wb, data, endrow=12, endrow_of_other=32)
        results_table(wb, data)
        relevant_table(wb, data, endrow=17, endrow_of_other=16)
        history_table(wb, data, endrow=159, endrow_of_other=144)
    except Exception:
        raise ValueError('上传文件非模板文件')

    bool_to_str(data)

    # data is file; is_updated is obj
    try:
        updated_data = checked_id(data, request_param=request_param)
    except Exception:
        raise

    if updated_data:
        # update
        msg = update_model(updated_data, data)
    else:
        # save
        msg = mobile_save_instance(data, e_type='save_excel', sf=sf)
        if not isinstance(msg, bool):
            raise ValueError(msg)
        msg = '数据创建成功'

    return msg
